*****************************
*** CREATE CHURCH DATASET ***
*****************************

/* dataset available after signing non-discloure agreement with Church of Sweden */
import excel "input/churchsurvey2.xlsx", firstrow clear

* create dummy variable for activities towards refugees (question 1)
gen scactivity=.

foreach i of num 2/23 {
replace scactivity=1 if Q1_`i'==1
}

replace scactivity=0 if Q1_1==1 | Q1_24==1 


* cretae variable for number of volunteers
destring Q4, gen(scvolunteers)

replace scvolunteers=. if scvolunteers==-111

* create dummy variable for money collection
gen scdonation=.

foreach i of num 2/6 {
replace scdonation=1 if Q9_`i'==1
}

replace scdonation=0 if Q9_1==1 | Q9_7==1 

* change name of municipalities to match with click dataset
rename Municipality municipality

loc s municipality
        replace `s' = subinstr(`s', "Ä", "A", .)
        replace `s' = subinstr(`s', "Å", "A", .)
        replace `s' = subinstr(`s', "Ö", "O", .)
        replace `s' = subinstr(`s', "å", "a", .)
        replace `s' = subinstr(`s', "ä", "a", .)
        replace `s' = subinstr(`s', "ö", "o", .)
		
replace municipality="Gothenburg" if municipality=="Goteborg"
replace municipality="Malung" if municipality=="Malung-Salen"
replace municipality="Ostra goinge" if municipality=="Ostra Goinge"
		
* keep only relevant variables 
keep municipality Parishcode scactivity scemployees scvolunteers scdonation

* construct weight based on complying parishes for activity and donation
bysort municipality: egen numparish=count(Parishcode)
foreach i in scactivity scdonation{
bysort municipality: egen numcomplier_`i'=sum(`i') 
bysort municipality: gen weight_`i'= numcomplier_`i'/numparish
}

* summing up number of volunteers and taking average for activity and donation by municipality
collapse (sum) scvolunteers (mean) scactivity scdonation weight_scactivity weight_scdonation, by(municipality)

sort municipality

save "churchsurvey.dta", replace
